-----------------------------SP-----------------------------
USE TOURS
GO
CREATE TYPE dtHotel AS TABLE
(
	avaiableRoom int
	, hotelID int
	, hotelName varchar(50)
	, price money
	, star int
	, room int
	, address varchar(200)
	, phone varchar(20)
	, hotelDescription varchar(MAX)
)--Drop PROC PR_LIST_HOTEL
GO
CREATE PROC PR_LIST_HOTEL
	@listHotel dtHotel READONLY
AS
	CREATE TABLE #temp(stt INT, hotelID INT,hotelName varchar(50),price int,star int,address varchar(100),phone varchar(20),hotelDescription varchar(MAX), pathLink VARCHAR(100))
	
	DECLARE @stt INT
	DECLARE @hotelID INT,@hotelName varchar(50),@price int,@star int,@address varchar(100),@phone varchar(20),@hotelDescription varchar(MAX),@pathLink VARCHAR(100)
	DECLARE @Cur CURSOR
	
	--IF @listHotelID=-1
		--Set @Cur=CURSOR FOR SELECT DISTINCT hotelID,hotelName,price,star,address,phone,hotelDescription FROM Hotels
	--ELSE
		Set @Cur=CURSOR FOR SELECT DISTINCT hotelID,hotelName,price,star,address,phone,hotelDescription FROM Hotels WHERE hotelID IN (SELECT hotelID FROM @listHotel)
	OPEN @Cur
		FETCH NEXT FROM @Cur INTO @hotelID,@hotelName,@price,@star,@address,@phone,@hotelDescription
		SET @stt=1
		WHILE(@@FETCH_STATUS=0)
		BEGIN
			SET @pathLink = (SELECT top 1 pathLink FROM Images WHERE type = 2 and foreignID = @hotelID)
			INSERT INTO #temp VALUES (@stt,@hotelID,@hotelName,@price,@star,@address,@phone,@hotelDescription,@pathLink)
				FETCH NEXT FROM @Cur INTO @hotelID,@hotelName,@price,@star,@address,@phone,@hotelDescription
			SET @stt=@stt+1
		END	
	SELECT * FROM #temp 
	CLOSE @Cur
	DEALLOCATE @Cur
GO	
		
		
--Drop PROC PR_LIST_TOUR
GO
CREATE PROC PR_LIST_TOUR
	@from varchar(50)
	,@to varchar(50)
	,@departDate varchar(50)
	,@returnDate varchar(50)
AS
	CREATE TABLE #temp(stt INT, tourID INT,tourCode char(5), tourName varchar(50),race int,placeStart varchar(30),placeEnd varchar(30),
		startDate Datetime, endDate Datetime,intervalTime float, price float, sellPrice float, itinerary varchar(100),status bit, pathLink VARCHAR(100))
	
	DECLARE @stt INT
	DECLARE @tourID INT,@tourCode char(5), @tourName varchar(50),@race int,@placeStart varchar(30),@placeEnd varchar(30),
		@startDate Datetime, @endDate Datetime,@intervalTime float, @price float, @sellPrice float, @itinerary varchar(100),@status bit,@pathLink VARCHAR(100)
	DECLARE @Cur CURSOR
	
	Set @Cur=CURSOR FOR SELECT DISTINCT T.tourID,tourCode,tourName,race,placeStart,placeEnd,startDate,endDate,
							T.intervalTime,T.price,sellPrice,itinerary,status FROM Tours T JOIN Tour_Hotel TH ON T.tourID = TH.tourID JOIN Hotels H ON TH.hotelID=H.hotelID 
							WHERE T.status = 1 AND T.placeStart LIKE @from AND T.placeEnd LIKE @to 
								AND CONVERT(varchar(20),T.startDate,112)>=@departDate AND CONVERT(varchar(20),T.endDate,112)<=@returnDate
	OPEN @Cur
		FETCH NEXT FROM @Cur INTO @tourID,@tourCode,@tourName,@race,@placeStart,@placeEnd,@startDate,@endDate,
									@intervalTime,@price,@sellPrice,@itinerary,@status
		SET @stt=1
		WHILE(@@FETCH_STATUS=0)
		BEGIN
			SET @pathLink = (SELECT top 1 pathLink FROM Images WHERE type = 1 and foreignID = @tourID)
			INSERT INTO #temp VALUES (@stt,@tourID,@tourCode,@tourName,@race,@placeStart,@placeEnd,@startDate,@endDate,
											@intervalTime,@price,@sellPrice,@itinerary,@status,@pathLink)
				FETCH NEXT FROM @Cur INTO @tourID,@tourCode,@tourName,@race,@placeStart,@placeEnd,@startDate,@endDate,
											@intervalTime,@price,@sellPrice,@itinerary,@status
			SET @stt=@stt+1
		END	
	SELECT Top 10 * FROM #temp 
	CLOSE @Cur
	DEALLOCATE @Cur
GO	

--Test: 
--EXEC PR_LIST_TOUR '%', '%' , '00010101','99990101'


--DROP PROC PR_SEARCH_HOTEL
GO
CREATE PROC PR_SEARCH_HOTEL
	  @departDate varchar(20)
	, @returnDate varchar(20)
	, @room int
AS
	SELECT H.room avaiable_room,* 
		FROM Hotels H 
		WHERE H.hotelID NOT IN (SELECT hotelID FROM Tour_Hotel) AND H.room>@room
	UNION
	SELECT room-numRoom avaiable_room,H.* 
		FROM Hotels H
			JOIN Tour_Hotel TH ON H.hotelID = TH.hotelID
		WHERE   (room-numRoom)>@room
					AND
				(CONVERT(varchar(20),TH.departDate,112)>@returnDate) 
					OR (CONVERT(varchar(20),TH.returnDate,112)<@departDate) 
					OR (
							(room-numRoom)>0 
							AND
							(
									(@departDate BETWEEN CONVERT(varchar(20),TH.departDate,112) AND CONVERT(varchar(20),TH.returnDate,112))
								OR  (@returnDate BETWEEN CONVERT(varchar(20),TH.departDate,112) AND CONVERT(varchar(20),TH.returnDate,112))
								OR  (@departDate<CONVERT(varchar(20),TH.departDate,112) AND CONVERT(varchar(20),TH.returnDate,112)<@returnDate)
							)
						)
GO	

--Test: 
--EXEC PR_SEARCH_HOTEL '20110805','20110815', 5